﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using A=System.Data;
using System.Data.SqlClient;
using System.Data.Sql;

namespace Test.DAL
{
    class sqlHelper
    {
        //创建链接字符串
        public static string sqlConn = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;


        public static A.DataTable Query(string sql, params SqlParameter[] sqlParameters)
        {
            //创建DataTable对象
            A.DataTable table = new A.DataTable();
            //创建sqldataadapter对象
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, sqlConn);
            if (sqlParameters.Length > 0)
            {
                dataAdapter.SelectCommand.Parameters.AddRange(sqlParameters);
            }
            dataAdapter.Fill(table);
            return table;
        }

        public static SqlDataReader Read(string sql,params SqlParameter[] sqlParameters) {
            SqlConnection sqlConnection = new SqlConnection(sqlConn);
            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            if (sqlParameters.Length>0)
            {
                sqlCommand.Parameters.AddRange(sqlParameters);
            }
            SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(A.CommandBehavior.CloseConnection);
            //sqlDataReader.Close();
            return sqlDataReader;
        }

        public static int NonQuery(string sql,A.CommandType type = A.CommandType.Text, params SqlParameter[] sqlParameters)
        {
            int num = 0;
            //创建链接对象
            using (SqlConnection sqlConnection = new SqlConnection(sqlConn))
            {
                //创建sqlcommand对象
                using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
                {
                    sqlCommand.CommandType = type;
                    try
                    {
                        //打开数据库链接
                        sqlConnection.Open();
                        if (sqlParameters.Length>0)
                        {
                            sqlCommand.Parameters.AddRange(sqlParameters);
                        }
                        //执行sql语句，返回受影响的行数
                        num = sqlCommand.ExecuteNonQuery();
                    }
                    finally//finally里面的内容最终都会执行
                    {
                        //判断数据库是否是打开状态
                        if (sqlConnection.State == A.ConnectionState.Open)
                            sqlConnection.Close();
                    }
                    return num;
                }
            }
        }
    }
}
